/*************************************************************************
    > 文件名: mysqlite3.c
    > 笔者: 田知足
    > 功能:  
    > 创建时间: 2018年08月23日 星期四 20时11分42秒
 ************************************************************************/
#include <mysqlite3.h>
static struct sqlite3 * mysql = NULL;		//数据库指针
static char * pErrMsg = NULL;				//出错信息打印
int use_mysql()
{
	int ret = 0;
	ret = openmysql();
	if(-1 == ret)
	{
		return -1;
	}
	ret = create_record_info_table();
	if(-1 == ret)
		return -2;
	ret = create_user_password_table();
	if(-1 == ret)
		return -2;
	ret = create_cordid_table();
	if(-1 == ret)
		return -2;
	return 0;
}
int openmysql()							//创建打开数据库
{
	int ret = sqlite3_open(PATH,&mysql);
	if(SQLITE_OK != ret)
	{
		printf("建表或打开失败\n");
		return -1;
	}
	return 0;
}

int create_record_info_table()			//创建数据记录表
{
	int ret = 0;
	ret = sqlite3_exec(mysql,"create table record_info_table(temperature text null,humidity text null,illumination text null,airfan text null,sunshade text null,spraywater text null,nowdate text null,nowtime text null)",0,0,&pErrMsg);
	if(SQLITE_OK != ret)
	{
//		printf("%s",pErrMsg);
		sqlite3_free(pErrMsg);
		return -1;
	}
	sqlite3_free(pErrMsg);
	return 0;
}
 
int create_user_password_table()		//创建用户信息表
{
	int ret = 0;
	ret = sqlite3_exec(mysql,"create table user_password_table(user_name text unique null,password text null)",0,0,&pErrMsg);
	if(SQLITE_OK != ret)
	{
//		printf("%s",pErrMsg);
		sqlite3_free(pErrMsg);
		return -1;
	}
	sqlite3_free(pErrMsg);
	return 0;
}

int create_cordid_table()				//创建门禁id表
{
	int ret = 0;
	ret = sqlite3_exec(mysql,"create table door_password_table(cardid text unique null)",0,0,&pErrMsg);
	if(SQLITE_OK != ret)
	{
//		printf("%s",pErrMsg);
		sqlite3_free(pErrMsg);
		return -1;
	}
	sqlite3_free(pErrMsg);
	return 0;
}

int insert_default_user_password_table()//创建默认用户、默认门禁id
{
	int ret = 0;
	ret = sqlite3_exec(mysql,"insert into door_password_table(cardid) values('1')",0,0,&pErrMsg);
	if(-1 == ret)
	{
		printf("%s",pErrMsg);
		sqlite3_free(pErrMsg);
		return -1;
	}
	sqlite3_free(pErrMsg);
	ret = sqlite3_exec(mysql,"insert into user_password_table(user_name,password) values('1','1')",0,0,&pErrMsg);
	if(-1 == ret)
	{
		printf("%s",pErrMsg);
		sqlite3_free(pErrMsg);
		return -1;
	}
	sqlite3_free(pErrMsg);
	return 0;
}
										//插入数据记录信息
int insert_record_info_table(server_to_client * date)
{
	char * sql1 = (char *)malloc(1000);
	int ret = 0,out = 0;
	memset(sql1,0,400);
	sprintf(sql1,"insert into record_info_table(temperature,humidity,illumination,airfan,sunshade,spraywater,nowdate,nowtime) values ('%.2f','%.2f','%.2f','%d','%d','%d','%s','%s')",\
			date->env_data.tem,date->env_data.hum,date->env_data.lux,\
			date->dev.air_fan,date->dev.sunshade,date->dev.spray_water,\
			date->nowtime.nowDate,date->nowtime.nowTime);
	ret = sqlite3_exec(mysql,sql1,0,0,&pErrMsg);
	if(SQLITE_OK != ret)
	{
		printf("%s",pErrMsg);
		out = -1;
	}
	free(sql1);
	return out;
}
										//用户密码验证
int user_password_compare(char * user_name, char * password)
{
	int n1 = 0,n2 = 0,ret = 0;
	char **p = NULL; 
	char sql[100] = {0};
	sprintf(sql,"select password from user_password_table where user_name = '%s'",user_name);
	ret = sqlite3_get_table(mysql,sql,&p,&n1,&n2,&pErrMsg);
	if(SQLITE_OK != ret)
	{
		puts(pErrMsg);
		sqlite3_free(pErrMsg);
		return -1;
	}
	sqlite3_free(pErrMsg);
	if(0 == n1)
		return -1;
	if(0 == strcmp(p[1],password))
		return 0;
	return -1;
}
										//判定门禁id
int cordid_compare(int id)
{
	int n1 = 0,n2 = 0,ret = 0;
	char sql[100] = {0};
	char ** p = NULL;
	sprintf(sql,"select cardid from door_password_table where cardid = '%d'",id);
	ret = sqlite3_get_table(mysql,sql,&p,&n1,&n2,&pErrMsg);
	if(SQLITE_OK != ret)
	{
		puts(pErrMsg);
		sqlite3_free(pErrMsg);
		return -1;
	}
	sqlite3_free(pErrMsg);
	if(0 < n1)
		return 0;
	return -1;
}

int break_day_from_data(char * day)
{
	int ret  = 0;
	int num  = atoi(day);
	if((0 == num%4 && 0 != num%100) || (0 == num%400))
		ret = 1;
	else
		ret = 0;
	num = atoi(day+5);
	if(4 == num ||6 == num||9 == num||11 == num)
		return 30;
	else if(2 == num)
	{
		if(1 == ret)
			return 29;
		else
			return 28;
	}
	else
		return 31;

}

										//删除数据记录信息
										//按天删除
int delete_day_record_info_table(break_date * plist,char * day)
{
	char sql[100] = {0};
	int ret = 0;
	ret = search_record_info_table(plist,day);
	if( 0 != ret)
		return -1;
	sprintf(sql,"delete from record_info_table where nowdate = '%s'",day);
	ret = sqlite3_exec(mysql,sql,0,0,&pErrMsg);
	if(0 != ret)
	{
		puts(pErrMsg);
		ret = -1;
	}
	sqlite3_free(pErrMsg);
	return ret;
}

int delete_record_info_table(break_date * plist,char * day)
{
	int ret = 0;
	ret = search_record_info_table(plist,day);
	if(-1 == ret)
		return -1;
	if('-' == day[7])
	{
		no_back_delete(day);
		return ret;
	}
	int num = break_day_from_data(day);
	char sql[20] = {0};
	for(ret = 1;ret <= num;ret++)
	{
		if(ret <= 9)
		{
			sprintf(sql,"%s-0%c",day,ret+'0');

		}
		else
		{
			sprintf(sql,"%s-%c%c",day,ret/10+'0',ret%10+'0');
		}
		no_back_delete(sql);
	}
	return 0;
}
int no_back_delete(char * day)
{
	char sql[200] = {0};
	sprintf(sql,"delete from record_info_table where nowdate = '%s'",day);
	int ret = sqlite3_exec(mysql,sql,0,0,&pErrMsg);
	if(SQLITE_OK != ret)
	{
		puts(pErrMsg);
		ret = -1;
	}
	sqlite3_free(pErrMsg);
	return 0;
}

//查找数据记录信息
int search_record_info_table(break_date * head,char * day)
{
	server_to_client date;
	char sql[100] = {0};
	char ** pl;
	int m1,m2,n1,n2;
	sprintf(sql,"select * from record_info_table where nowdate like '%s",day);
	strcat(sql,"%'");
	int	ret = sqlite3_get_table(mysql,sql,&pl,&n1,&n2,&pErrMsg);
	if(SQLITE_OK != ret)
	{
		puts(pErrMsg);
		sqlite3_free(pErrMsg);
		return -1;
	}
	sqlite3_free(pErrMsg);
	if (0 == n1)
		return 1;
	for(m1 = 1;m1 <= n1;m1++)
	{
		m2 = 0;
		switch(m2)
		{	
		case 0:
				date.env_data.tem = atof(pl[m1*n2+m2]);
				m2++;
		case 1:
				date.env_data.hum = atof(pl[m1*n2+m2]);
				m2++;
		case 2:
				date.env_data.lux = atof(pl[m1*n2+m2]);
				m2++;
		case 3:
				date.dev.air_fan = pl[m1*n2+m2][0];
				m2++;
		case 4:
				date.dev.sunshade = pl[m1*n2+m2][0];
				m2++;
		case 5:
				date.dev.spray_water = pl[m1*n2+m2][0];
				m2++;
		case 6:
				strcpy(date.nowtime.nowDate ,pl[m1*n2+m2]);
				m2++;
		default:
				strcpy(date.nowtime.nowTime ,pl[m1*n2+m2]);
		}
		insert_break_date(head,&date);
	}
	return 0;
}
//创建链表表头
break_date * create_break_date()
{
	break_date * head = (break_date *) malloc (sizeof(break_date));
	if(NULL == head)
		return NULL;
	memset(head,0,sizeof(break_date));
	head->last = NULL;
	return head;
}
										//链表插入数据
int insert_break_date(break_date *head,server_to_client * date)
{
	if(NULL == head)
		return -1;
	break_date * newitem = (break_date *)malloc(sizeof(break_date));
	if(NULL == newitem)
		return -1;
	memset(newitem,0,sizeof(break_date));
	newitem->date = *date;
	newitem->last = head->last;
	head->last = newitem;
	return 0;
}
								//链表删除
int delete_break_date(break_date * head)
{
	break_date * del;
	while(1)
	{
		if(NULL != head->last)
		{
			del = head->last;
			head->last = del->last;
			free(del);
			del = NULL;
		}
		else
		{
			free(head);
			return 0;
		}
	}
}
			//测试用链表打印，打印单个
void look(break_date * head)
{
	printf("tem = %.2f	|hum = %.2f	|lux = %.2f\n",head->date.env_data.tem,head->date.env_data.hum,head->date.env_data.lux);
	printf("dev = %c		|sun = %c	|wat = %c\n",head->date.dev.air_fan,head->date.dev.sunshade,head->date.dev.spray_water);
	printf("date= %s	|time= %s",head->date.nowtime.nowDate,head->date.nowtime.nowTime);
	printf("\n***************\n");
}

void close_mysql()
{
	sqlite3_close(mysql);
}
		//更改密码
int add_user_password_table(admin * user)
{
	char sql1[200] = {0};
	sprintf(sql1,"insert into user_password_table(user_name,password) values ('%s','%s')",user->id,user->password);
	int ret = sqlite3_exec(mysql,sql1,0,0,&pErrMsg);
	if(SQLITE_OK != ret)
	{
		sqlite3_free(pErrMsg);
		return -1;
	}
	sqlite3_free(pErrMsg);
	return 0;
}
									//添加门禁卡
int add_card_id_table(int id)
{
	char sql1[200] = {0};
	sprintf(sql1,"insert into door_password_table(cardid) values ('%d')",id);
	int ret = sqlite3_exec(mysql,sql1,0,0,&pErrMsg);
	if(SQLITE_OK != ret)
	{
		sqlite3_free(pErrMsg);
		return -1;
	}
	sqlite3_free(pErrMsg);
	return 0;
}

